﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using RoadTeam.Model;
using RoadTeam.Enums;
using System.Data;

public partial class 分页_Page : System.Web.UI.Page
{
    #region 原先测试方法
    protected void Button3_Click(object sender, EventArgs e)
    {
        if (Session["i"] == null)
        {
            Session["i"] = 3000;
        }
        Session["i"] = 150;
        SqlWhere where = new SqlWhere();
        where.SelectColumn = @" Count(*) ";
        where.StrSqlWhere = @" UnitCode<>'-1'  ";
        DataTable dt = RmUnit.Select(where, GlobalConfig.ConnectionString);

        int AllCount = dt.Rows.Count == 0 ? 0 : Convert.ToInt32(dt.Rows[0][0]);
        RmUnit Unit = new RmUnit();
        QueryPage page = new QueryPage();
        page.AllCount = AllCount;
        page.Where = new OQLWhere(Unit).AND(Unit.UnitCode, "<>", "-1");
        page.PageSize = 20;
        int i = Convert.ToInt32(Session["i"]);
        page.CurrentPage = i;
        ++i;
        Session["i"] = i;
        string strSQLInfo = @" Select * from RmUnit Where 1=1  Order By  UnitCode ";
        String Str = MakePageSQLStringByMSSQL(strSQLInfo, page.Where.StrWhere, page.PageSize, page.CurrentPage, page.AllCount);
        Response.Write(Str);
    }
    private string MakePageSQLStringByMSSQL(string strSQLInfo, string strWhere, int PageSize, int PageNumber, int AllCount)
    {
        #region 分页位置分析
        string strSQLType = string.Empty;
        if (AllCount > 0)
        {
            if (PageNumber == 1) //首页
            {
                strSQLType = "First";
            }
            else if (PageSize * PageNumber > AllCount) //最后的页 @@LeftSize
            {
                PageSize = AllCount - PageSize * (PageNumber - 1);
                strSQLType = "Last";
            }
            else //中间页
            {
                strSQLType = "Mid";
            }
        }
        else if (AllCount < -1) //特殊处理 
        {
            strSQLType = "First";
        }
        else
        {
            strSQLType = "Count";//0 or -1
        }

        #endregion

        #region SQL 复杂度分析
        //SQL 复杂度分析 开始
        bool SqlFlag = true;//简单SQL标记
        string TestSQL = strSQLInfo.ToUpper();
        int n = TestSQL.IndexOf("SELECT ", 0);
        n = TestSQL.IndexOf("SELECT ", n + 7);
        if (n == -1)
        {
            //可能是简单的查询，再次处理
            n = TestSQL.IndexOf(" JOIN ", n + 7);
            if (n != -1) SqlFlag = false;
            else
            {
                //判断From 谓词情况
                n = TestSQL.IndexOf("FROM ", 9);
                if (n == -1) return "";
                //计算 WHERE 谓词的位置
                int m = TestSQL.IndexOf("WHERE ", n + 5);
                // 如果没有WHERE 谓词
                if (m == -1) m = TestSQL.IndexOf("ORDER BY ", n + 5);
                //如果没有ORDER BY 谓词，那么无法排序，退出；
                if (m == -1)
                    throw new Exception("查询语句分析：当前没有为分页查询指定排序字段！请适当修改SQL语句。\n" + strSQLInfo);
                string strTableName = TestSQL.Substring(n, m - n);
                //表名中有 , 号表示是多表查询
                if (strTableName.IndexOf(",") != -1)
                    SqlFlag = false;
            }
        }
        else
        {
            //有子查询；
            SqlFlag = false;
        }
        //SQL 复杂度分析 结束
        #endregion

        #region 排序语法分析
        //排序语法分析 开始
        int iOrderAt = strSQLInfo.ToLower().LastIndexOf("order by ");
        //如果没有ORDER BY 谓词，那么无法排序分页，退出；
        if (iOrderAt == -1)
        {
            if (PageNumber == 1)
            {
                string sqlTemp = "Select Top @@PageSize * FROM ( " + strSQLInfo +
                       " ) P_T0 @@Where ";
                return sqlTemp.Replace("@@PageSize", PageSize.ToString()).Replace("@@Where", strWhere);
            }
            else
            {
                throw new Exception("查询语句分析：当前没有为分页查询指定排序字段！请适当修改SQL语句。\n" + strSQLInfo);
            }
        }

        string strOrder = strSQLInfo.Substring(iOrderAt + 9);
        strSQLInfo = strSQLInfo.Substring(0, iOrderAt);
        string[] strArrOrder = strOrder.Split(new char[] { ',' });
        for (int i = 0; i < strArrOrder.Length; i++)
        {
            string[] strArrTemp = (strArrOrder[i].Trim() + " ").Split(new char[] { ' ' });
            //压缩多余空格
            for (int j = 1; j < strArrTemp.Length; j++)
            {
                if (strArrTemp[j].Trim() == "")
                {
                    continue;
                }
                else
                {
                    strArrTemp[1] = strArrTemp[j];
                    if (j > 1) strArrTemp[j] = "";
                    break;
                }
            }
            //判断字段的排序类型
            switch (strArrTemp[1].Trim().ToUpper())
            {
                case "DESC":
                    strArrTemp[1] = "ASC";
                    break;
                case "ASC":
                    strArrTemp[1] = "DESC";
                    break;
                default:
                    //未指定排序类型，默认为降序
                    strArrTemp[1] = "DESC";
                    break;
            }
            //消除排序字段对象限定符
            if (strArrTemp[0].IndexOf(".") != -1)
                strArrTemp[0] = strArrTemp[0].Substring(strArrTemp[0].IndexOf(".") + 1);
            strArrOrder[i] = string.Join(" ", strArrTemp);

        }
        //生成反向排序语句
        string strNewOrder = string.Join(",", strArrOrder).Trim();
        strOrder = strNewOrder.Replace("ASC", "ASC0").Replace("DESC", "ASC").Replace("ASC0", "DESC");
        //排序语法分析结束
        #endregion

        #region 构造分页查询
        string SQL = string.Empty;
        if (!SqlFlag)
        {
            //复杂查询处理
            switch (strSQLType.ToUpper())
            {
                case "FIRST":
                    SQL = "Select Top @@PageSize * FROM ( " + strSQLInfo +
                        " ) P_T0 @@Where ORDER BY " + strOrder;
                    break;
                case "LAST":
                case "MID":
                    string strRep = "SELECT SELECT# FROM (" + strSQLInfo + ") AS P_T0 @@Where"; //重要，必须放置在此。
                    strRep = strRep.Replace("SELECT#", "ROW_NUMBER() OVER (ORDER BY " + strOrder + ") AS SYS_PG_0000,*");
                    SQL = "SELECT * FROM (" + strRep + ") AS P_T1 WHERE SYS_PG_0000 > @@Page_Size_Number_Start AND SYS_PG_0000 <= @@Page_Size_Number_End";
                    SQL = SQL.Replace("@@Page_Size_Number_Start", Convert.ToString((PageNumber - 1) * PageSize));
                    SQL = SQL.Replace("@@Page_Size_Number_End", Convert.ToString((PageNumber) * PageSize));
                    //                        SQL = @"SELECT Top @@PageSize * FROM
                    //                         (SELECT Top @@PageSize * FROM
                    //                           (
                    //                             SELECT Top @@Page_Size_Number * FROM (";
                    //                        SQL += " " + strSQLInfo + " ) P_T0 @@Where ORDER BY " + strOrder + " ";
                    //                        SQL += @") P_T1
                    //            ORDER BY " + strNewOrder + ") P_T2  " +
                    //                            "ORDER BY " + strOrder;
                    break;
                //                    case "LAST":
                //                        SQL = @"SELECT * FROM (     
                //                          Select Top @@LeftSize * FROM (" + " " + strSQLInfo + " ";
                //                        SQL += " ) P_T0 @@Where ORDER BY " + strNewOrder + " " +
                //                            " ) P_T1 ORDER BY " + strOrder;
                //                        break;
                case "COUNT":
                    SQL = "SELECT COUNT(1) FROM ( " + strSQLInfo + " ) P_Count @@Where";
                    break;
                default:
                    SQL = strSQLInfo + strOrder;//还原
                    break;
            }

        }
        else
        {
            //简单查询处理
            string strUpperSQLInfo = strSQLInfo.ToUpper();
            bool isDistinct = strUpperSQLInfo.IndexOf("DISTINCT") >= 7;
            if (isDistinct)
                strUpperSQLInfo = strUpperSQLInfo.Replace("DISTINCT", "");
            switch (strSQLType.ToUpper())
            {
                case "FIRST":
                    if (isDistinct)
                        SQL = strUpperSQLInfo.Replace("DISTINCT", "DISTINCT TOP @@PageSize");
                    else
                        SQL = strUpperSQLInfo.Replace("SELECT ", "SELECT TOP @@PageSize ");
                    SQL += "  @@Where ORDER BY " + strOrder;
                    break;
                case "LAST":
                case "MID":
                    string strRep = "SELECT SELECT# FROM (" + strSQLInfo + "@@Where" + ") AS A WHERE 1=1"; //重要，必须放置在此。
                    if (isDistinct)
                        strRep = strRep.Replace("SELECT#", "DISTINCT SELECT#");
                    strRep = strRep.Replace("SELECT#", "ROW_NUMBER() OVER (ORDER BY " + strOrder + ") AS SYS_PG_0000,*");
                    SQL = "SELECT * FROM (" + strRep + ") AS P_T0 WHERE SYS_PG_0000 > @@Page_Size_Number_Start AND SYS_PG_0000 <= @@Page_Size_Number_End";
                    SQL = SQL.Replace("@@Page_Size_Number_Start", Convert.ToString((PageNumber - 1) * PageSize));
                    SQL = SQL.Replace("@@Page_Size_Number_End", Convert.ToString((PageNumber) * PageSize));
                    break;
                case "COUNT":
                    SQL = "SELECT COUNT(*) FROM ( " + strSQLInfo + " @@Where) P_Count ";
                    break;
                default:
                    SQL = strSQLInfo + strOrder;//还原
                    break;
            }
        }

        //执行分页参数替换
        if (PageSize < 0) PageSize = 0;
        SQL = SQL.Replace("@@PageSize", PageSize.ToString())
            .Replace("@@Page_Size_Number", Convert.ToString(PageSize * PageNumber))
            .Replace("@@LeftSize", PageSize.ToString());//
        //.Replace ("@@Where",strWhere);
        //针对用户的额外条件处理：
        if (strWhere == null) strWhere = "";
        if (strWhere != "" && strWhere.ToUpper().Trim().StartsWith("WHERE "))
        {
            throw new Exception("分页额外查询条件不能带Where谓词！");
        }
        if (!SqlFlag)
        {
            if (strWhere != "") strWhere = " Where " + strWhere;
            SQL = SQL.Replace("@@Where", strWhere);
        }
        else
        {
            if (strWhere != "") strWhere = " And (" + strWhere + ")";
            SQL = SQL.Replace("@@Where", strWhere);
        }
        return SQL;
        #endregion

    }

    #endregion
    QueryPage page = new QueryPage();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Label4.Text = "0";
            BindGvList(page, 0);
        }

    }
    public void BindGvList(QueryPage page, int i)
    {
        RmUnit unit = new RmUnit();
        int CurrentPage = String.IsNullOrEmpty(Label4.Text) ? 0 : Convert.ToInt32(Label4.Text);
        CurrentPage = CurrentPage + i;
        page.CurrentPage = CurrentPage;
        page.PageSize = 20;
        page.OrderByColumn = @"UnitCode"; 
        page.Where = new OQLWhere(unit).AND(unit.OrganIdentifier, "=", "302c2a6eb6104a07bb8db51b1d1c83b8");
        DataTable dt = RmUnit.Select(page, GlobalConfig.ConnectionString);
        GridView1.DataSource = dt;
        GridView1.DataBind();

        Label4.Text = page.CurrentPage.ToString();
        Label3.Text = page.AllCount.ToString();
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        BindGvList(page, -1);
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        BindGvList(page, 1);
    }
}